<?php
class KqbanhangxlsController extends AppController {

	var $name = 'Kqbanhangxls';
	var $helpers = array('Html', 'Form', 'FileUpload.FileUpload');
    var $components = array('FileUpload.FileUpload');
    var $uses=array('Kqbanhangxl','Kqbanhang','Loaikenh','Kqbanhangttxl','Kqbanhangtt','Chitieudv');
  
   
   /**
    * hien thi cac cot
    * 1/makenh, ten kenh
    * 2/ma sp, tensp
    * 3/soluong
    * 
    * order by theo makenh, masp
    * */
	function index() {
	   
        $sql="
                select kqbanhangxls.id,makenh, tenkenh, mahh, tenhh,kqbanhangxls.soluong
                from kenhs, hanghoas, kqbanhangxls, phankenhs, chitieunvs
                where kqbanhangxls.phankenh_id=phankenhs.id
                    and phankenhs.kenh_id=kenhs.id
                    and chitieunvs.hanghoa_id=hanghoas.id 
                    and chitieunvs.id=kqbanhangxls.chitieunv_id";
		$kqbanhangxls=$this->Kqbanhangxl->query($sql);
         //kqbanhangtt gom chitieudv,loaikenh_id, ngay, soluong, baocao
        
        $sql="
                select  loaikenh_id,mahh, tenhh,sum(kqbanhangttxls.soluong) as sl
                from  hanghoas, kqbanhangttxls,chitieudvs
                where chitieudvs.hanghoa_id=hanghoas.id 
                    and chitieudvs.id=kqbanhangttxls.chitieudv_id
                group by loaikenh_id,mahh, tenhh";
		$kqbanhangttxls=$this->Kqbanhangxl->query($sql);
        
        $loaikenh=$this->Loaikenh->find('first',array(
            'conditions'=>array('id'=>$kqbanhangttxls [0] ['kqbanhangttxls'] ['loaikenh_id']),
            'fields'=>array('loaikenh'),'recursive'));
       // debug($kqbanhangttxls);
        
		$this->set(compact('kqbanhangxls','kqbanhangttxls','loaikenh'));
	}
    function indexe() {
	 $sql="
                select kqbanhangxls.id,makenh, tenkenh, mahh, tenhh,kqbanhangxls.soluong
                from kenhs, hanghoas, kqbanhangxls, phankenhs, chitieunvs
                where kqbanhangxls.phankenh_id=phankenhs.id
                    and phankenhs.kenh_id=kenhs.id
                    and chitieunvs.hanghoa_id=hanghoas.id 
                    and chitieunvs.id=kqbanhangxls.chitieunv_id";
		$kqbanhangxls=$this->Kqbanhangxl->query($sql);
         //kqbanhangtt gom chitieudv,loaikenh_id, ngay, soluong, baocao
        
        $sql="
                select  loaikenh_id,mahh, tenhh,sum(kqbanhangttxls.soluong) as sl
                from  hanghoas, kqbanhangttxls,chitieudvs
                where chitieudvs.hanghoa_id=hanghoas.id 
                    and chitieudvs.id=kqbanhangttxls.chitieudv_id
                group by loaikenh_id,mahh, tenhh";
		$kqbanhangttxls=$this->Kqbanhangxl->query($sql);
        
        $loaikenh=$this->Loaikenh->find('first',array(
            'conditions'=>array('id'=>$kqbanhangttxls [0] ['kqbanhangttxls'] ['loaikenh_id']),
            'fields'=>array('loaikenh'),'recursive'));
       // debug($kqbanhangttxls);
        
		$this->set(compact('kqbanhangxls','kqbanhangttxls','loaikenh'));
	}
    function luu(){
        //neu cung la 1 san pham thi cong lai so luong ban
   	   
       /** ******************************************************************************
        * Bắt đầu lưu của nhân viên
        * */
       $kqbanhangxls=$this->Kqbanhangxl->find('all',array(
                'group'=>array('chitieunv_id','phankenh_id','ngay','dabaocao'),
                'fields'=>array('chitieunv_id','phankenh_id','ngay','dabaocao','sum(soluong) as sl'),
                'recursive'=>-1));
      // debug($kqbanhangxls);
       //kqbanhangtt gom chitieudv,loaikenh, ngay, soluong, baocao
       $kehoachnv=$this->Kqbanhang->query("select kehoach_id from chitieunvs 
                where id=".$kqbanhangxls [0] ['Kqbanhangxl'] ['chitieunv_id']);
                  
        foreach($kqbanhangxls as $kq){
            //lay du lieu
            /**
             * van de o cho chi cap nhat theo ngay (ngay cu se bi bo thay bang ngay moi)
             * */
            $k=$this->Kqbanhang->find('first',
                array(
                        'conditions'=>array('chitieunv_id'=>$kq ['Kqbanhangxl'] ['chitieunv_id'],
                            'phankenh_id'=>$kq ['Kqbanhangxl'] ['phankenh_id'],
                            'ngay'=>$kq ['Kqbanhangxl'] ['ngay']),
                        'recursive'=>-1,'fields'=>array('id')));
            //kiem tra ketqua ban hang nay da nhap chua
            if(empty($k)){
                //neu chua thi them
                $this->Kqbanhang->create();
                $r ['Kqbanhang'] ['chitieunv_id']=$kq['Kqbanhangxl'] ['chitieunv_id'];
                $r ['Kqbanhang'] ['phankenh_id']=$kq['Kqbanhangxl'] ['phankenh_id'];
                //day la ngay ket thuc
                $r ['Kqbanhang'] ['ngay']=$kq['Kqbanhangxl'] ['ngay'];
                $r ['Kqbanhang'] ['soluong']=$kq[0] ['sl'];
                $r ['Kqbanhang'] ['dabaocao']=1;
                              
                if(!$this->Kqbanhang->save($r)){
                    $this->Session->setFlash(__('Có lỗi khi thêm kết quả bán hàng của nhân viên', true));
       	            $this->redirect(array('controller'=>'kqbanhangs','action'=>'hienkqbhngay',$this->Session->read('cur_manv')));
                    
                }
                    
            }else{
                //neu roi thi sua
                if(!$this->Kqbanhangxl->query("
                    update kqbanhangs set soluong='".$kq[0] ['sl']."' , dabaocao=1
                            where id=".$k ['Kqbanhang'] ['id'])){
                    $this->Session->setFlash(__('Có lỗi khi cập nhật kết quả bán hàng của nhân viên', true));
       	            $this->redirect(array('controller'=>'kqbanhangs','action'=>'hienkqbhngay',$this->Session->read('cur_manv')));
                    
                }
            }
        }
        
       /*
        $ctieunvs=$this->Kqbanhang->find('list',array(
                'fields'=>array('chitieunv_id','chitieunv_id'),'recursive'=>-1));
        $kh=$this->Chitieunv->find('list',array('conditions'=>array('id'=>$ctieunvs),
                'fields'=>array('kehoach_id','kehoach_id'),'recursive'=>-1));        
        
        $hh=$this->Chitieunv->find('all',array(
                'conditions'=>array('NOT'=>array('id'=>$ctieunvs),
                                    'AND'=>array('kehoach_id'=>$kh)),
                'fields'=>array('id','hanghoa_id'),'recursive'=>-1));        
        foreach($hh as $h){
             $this->Kqbanhang->create();
                $r ['Kqbanhang'] ['chitieunv_id']=$h['Kqbanhang'] ['chitieunv_id'];
                $r ['Kqbanhang'] ['phankenh_id']=$h['Kqbanhang'] ['phankenh_id'];
                //day la ngay ket thuc
                $r ['Kqbanhang'] ['ngay']=$kq['Kqbanhangxl'] ['ngay'];
                $r ['Kqbanhang'] ['soluong']=0;
                $r ['Kqbanhang'] ['dabaocao']=1;
        }
        */
        
        
        $sql="
            insert into kqbanhangs (chitieunv_id, phankenh_id, ngay, soluong, dabaocao)
                select distinct c.id, h.id, 
                        '".date('Y-m-d',strtotime($kqbanhangxls [0] ['Kqbanhangxl'] ['ngay']))."',0,1
                
                from phankenhs h, chitieunvs c left join kqbanhangs k on c.id=k.chitieunv_id 
                where  c.nhanvien_id=h.nhanvien_id
                    and ngaykt_hd is null
                    and k.id is null
                    and c.kehoach_id=".$kehoachnv[0] ['chitieunvs'] ['kehoach_id'];
        
        if(!$this->Kqbanhang->query($sql))
                $this->Session->setFlash(__('Có lỗi khi lưu các hàng hóa không có dữ liệu của nhân viên', true));
       
       /**
        * Kết thúc lưu của nhân viên
        ************************************************************************************ */     
        
        //debug($s);
        
        
        /** ************************************************************************************
         * Bắt đầu lưu cho đơn vị
         * */
        $kqbanhangttxls=$this->Kqbanhangttxl->find('all',array(
                'group'=>array('chitieudv_id','loaikenh_id','ngay','dabaocao'),
                'fields'=>array('chitieudv_id','loaikenh_id','ngay','dabaocao','sum(soluong) as sl'),
                'recursive'=>-1));
      // debug($kqbanhangxls);
       //kqbanhangtt gom chitieudv,loaikenh, ngay, soluong, baocao
         $ng =null; 
        foreach($kqbanhangttxls as $kq){
            //lay du lieu
            /**
             * van de o cho chi cap nhat theo ngay (ngay cu se bi bo thay bang ngay moi)
             * */
            $k=$this->Kqbanhangtt->find('first',
                array(
                        'conditions'=>array('chitieudv_id'=>$kq ['Kqbanhangttxl'] ['chitieudv_id'],
                            'loaikenh_id'=>$kq ['Kqbanhangttxl'] ['loaikenh_id'],
                            'ngay'=>$kq ['Kqbanhangttxl'] ['ngay']),
                        'recursive'=>-1,'fields'=>array('id')));
            //kiem tra ketqua ban hang nay da nhap chua
            if(empty($k)){
                //neu chua thi them
                $this->Kqbanhangtt->create();
                $r ['Kqbanhangtt'] ['chitieudv_id']=$kq['Kqbanhangttxl'] ['chitieudv_id'];
                $r ['Kqbanhangtt'] ['loaikenh_id']=$kq['Kqbanhangttxl'] ['loaikenh_id'];
                //day la ngay ket thuc
                $r ['Kqbanhangtt'] ['ngay']=$kq['Kqbanhangttxl'] ['ngay'];
                $r ['Kqbanhangtt'] ['soluong']=$kq[0] ['sl'];
                $r ['Kqbanhangtt'] ['dabaocao']=1;
                              
                if(!$this->Kqbanhangtt->save($r)){
                    $this->Session->setFlash(__('Có lỗi khi thêm kết quả bán hàng ở đơn vị', true));
       	            $this->redirect(array('controller'=>'kqbanhangs','action'=>'hienkqbhngay',$this->Session->read('cur_manv')));
                    
                }
                
            }else{
                //neu roi thi sua
                if(!$this->Kqbanhangxl->query("
                    update kqbanhangtts set soluong='".$kq[0] ['sl']."' , dabaocao=1
                            where id=".$k ['Kqbanhangtt'] ['id'])){
                    $this->Session->setFlash(__('Có lỗi khi cập nhật bán hàng ở đơn vị', true));
       	            $this->redirect(array('controller'=>'kqbanhangs','action'=>'hienkqbhngay',$this->Session->read('cur_manv')));
                    
                }
            }
        }
        
        $kehoachdv=$this->Kqbanhang->query("select kehoach_id from chitieudvs where 
                    id=".$kqbanhangttxls [0]['Kqbanhangttxl'] ['chitieudv_id' ]);
        
        $sql="
            insert into kqbanhangtts (chitieudv_id, loaikenh_id, ngay, soluong, dabaocao)
                select distinct c.id,". $kqbanhangttxls [0] ['Kqbanhangttxl'] ['loaikenh_id'].", 
                        '".date('Y-m-d',strtotime($kqbanhangxls [0] ['Kqbanhangxl'] ['ngay']))."',0,1
                
                from chitieudvs c left join kqbanhangtts k on c.id=k.chitieudv_id 
                where c.kehoach_id=".$kehoachnv[0] ['chitieunvs'] ['kehoach_id'];
        
        if(!$this->Kqbanhang->query($sql))
          	$this->Session->setFlash(__('Có lỗi khi lưu các hàng hóa không có dữ liệu ở đơn vị', true));
            
        
        
        /** ket thuc luu don vi
         * ********************************************************************/
        //$kqbanhangxls=$this->Kqbanhangxl->query('truncate table kqbanhangxls');
        //$kqbanhangxls=$this->Kqbanhangxl->query('truncate table kqbanhangttxls');
       	
          	
       	$this->Session->setFlash(__('Dữ liệu đã được IMP thành công', true));
       	$this->redirect(array('controller'=>'kqbanhangs','action'=>'hienkqbhngay',$this->Session->read('cur_manv')));
    }
	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid Kqbanhangxl.', true));
			$this->redirect(array('action'=>'index'));
		}
		$this->set('kqbanhangxl', $this->Kqbanhangxl->read(null, $id));
	}

	function add() {
		if (!empty($this->data)) {
			$this->Kqbanhangxl->create();
			if ($this->Kqbanhangxl->save($this->data)) {
				$this->Session->setFlash(__('The Kqbanhangxl has been saved', true));
				$this->redirect(array('action'=>'index'));
			} else {
				$this->Session->setFlash(__('The Kqbanhangxl could not be saved. Please, try again.', true));
			}
		}
		$chitieunvs = $this->Kqbanhangxl->Chitieunv->find('list');
		$phankenhs = $this->Kqbanhangxl->Phankenh->find('list');
		$this->set(compact('chitieunvs', 'phankenhs'));
	}

	function edit($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid Kqbanhangxl', true));
			$this->redirect(array('action'=>'index'));
		}
		if (!empty($this->data)) {
			if ($this->Kqbanhangxl->save($this->data)) {
				$this->Session->setFlash(__('The Kqbanhangxl has been saved', true));
				$this->redirect(array('action'=>'index'));
			} else {
				$this->Session->setFlash(__('The Kqbanhangxl could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Kqbanhangxl->read(null, $id);
		}
		$chitieunvs = $this->Kqbanhangxl->Chitieunv->find('list');
		$phankenhs = $this->Kqbanhangxl->Phankenh->find('list');
		$this->set(compact('chitieunvs','phankenhs'));
	}

	function delete($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for Kqbanhangxl', true));
			$this->redirect(array('action'=>'index'));
		}
		if ($this->Kqbanhangxl->delete($id)) {
			$this->Session->setFlash(__('1 dòng đã được xóa', true));
			$this->redirect(array('action'=>'index'));
		}
	}
    function delete1($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for Kqbanhangxl', true));
			$this->redirect(array('action'=>'indexe'));
		}
		if ($this->Kqbanhangxl->delete($id)) {
			$this->Session->setFlash(__('1 dòng đã được xóa', true));
			$this->redirect(array('action'=>'indexe'));
		}
	}
}
?>